# -*- coding: utf-8 -*-
from buss_api.db.mysql_tools import MysqlHelper


class DbManage(object):
    def __init__(self):
        pass
        # super(self).__init__()

    ALL_TABLES = "SELECT TABLE_NAME as name, 'TABLE' type FROM information_schema.TABLES WHERE TABLE_SCHEMA = '{0}'  and TABLE_NAME like '{1}%%' ORDER BY TABLE_NAME;"
    ALL_COLS = "SELECT COLUMN_NAME name, COLUMN_TYPE type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{0}'  AND TABLE_NAME = '{1}' ORDER BY COLUMN_NAME; "
    TABLE_DETAIL = "show create table {0}"
    ALL_PROC = "SELECT ROUTINE_NAME name, ROUTINE_TYPE type, ROUTINE_DEFINITION content FROM INFORMATION_SCHEMA.ROUTINES WHERE (ROUTINE_TYPE = 'PROCEDURE' OR ROUTINE_TYPE = 'FUNCTION') and  ROUTINE_SCHEMA='{0}' ORDER BY ROUTINE_NAME;"
    PROC_DETAIL = "SHOW CREATE {0} `{1}`.`{2}`;"
    @classmethod
    def test_link(cls, params):
        MysqlHelper(params).get_version()

    @classmethod
    def check_tabs(cls, pam1, pam2):
        tabs = DbManage.select_all_tabls(pam1)
        for i, ele in enumerate(tabs):
            print(f"校验表={ele['name']}-----------------------------")
            res = DbManage.comparison_tab(pam1, pam2, ele['name'])
            print(f"校验表完成={res}")

    # ========================表==============================
    @classmethod
    def select_all_tabls(cls, params, query):
        mysql_tools = MysqlHelper(params)
        res = mysql_tools.get_all(DbManage.ALL_TABLES.format(params["db"],  query["name"]))
        return res

    @classmethod
    def select_all_tabls_cols(cls, params, tab):
        mysql_tools = MysqlHelper(params)
        res = mysql_tools.get_all(DbManage.ALL_COLS.format(params["db"], tab))
        return res

    @classmethod
    def comparison_tab(cls, param_src, param_target, tab):
        # 根据表名获取目标库中表的字段，如果返回空，那么说明表不存在
        # 获取源库中表字段
        # 判断字段条数是否一致
        # 逐一判断字段名称及类型
        res = ""
        target_cols = MysqlHelper(param_target).get_all(DbManage.ALL_COLS.format(param_target["db"], tab))
        if target_cols is None or len(target_cols) == 0:
            return "表不存在"
        else:
            src_cols = MysqlHelper(param_src).get_all(DbManage.ALL_COLS.format(param_src["db"], tab))
            # if len(src_cols) != len(target_cols):
            #     return "字段不一致"
            # else:
            for i, ele in enumerate(src_cols):
                if not DbManage.judge_have_col(ele, target_cols):
                    res += f"字段【{ele['name']}】不存在，或者类型不一致\n"
            return res

    @classmethod
    def judge_have_col(cls,  col, cols):
        for i, ele in enumerate(cols):
            if ele["name"] == col["name"] and ele["type"] == col["type"]:
                return True
        return False

    @classmethod
    def select_table(cls, link, name):
        mysql_tools = MysqlHelper(link)
        res = mysql_tools.get_one(DbManage.TABLE_DETAIL.format(name))
        return res["Create Table"] if res is not None else None

    @classmethod
    def create_table(cls, links, name):
        table = DbManage.select_table(links["src"], name)
        if table:
            mysql_tools = MysqlHelper(links["target"])
            tables = [f'DROP TABLE IF EXISTS `{name}`; ', table]
            mysql_tools.updates(tables)


    # ----------------------------过程----------------------------------------
    @classmethod
    def select_all_proc(cls, params):
        mysql_tools = MysqlHelper(params)
        res = mysql_tools.get_all(DbManage.ALL_PROC.format(params["db"]))
        return res


    @classmethod
    def comparison_proc(cls, proc, target_procs):
        # 根据表名获取目标库中表的字段，如果返回空，那么说明表不存在
        # 获取源库中表字段
        # 逐一判断字段名称
        if target_procs is None or len(target_procs) == 0:
            return "过程/函数不存在"
        else:
            for i, ele in enumerate(target_procs):
                if ele["name"] == proc:
                    return ""
            return f"过程/函数【{proc}】不存在"

    @classmethod
    def select_proc(cls, link, name, type):
        mysql_tools = MysqlHelper(link)
        res = mysql_tools.get_one(DbManage.PROC_DETAIL.format(type, link["db"], name))
        if type == 'FUNCTION':
            return res["Create Function"] if res is not None else None
        else:
            return res["Create Procedure"] if res is not None else None

    @classmethod
    def create_proc(cls, links, name, type):
        proc = DbManage.select_proc(links["src"], name, type)
        if proc:
            proc = proc.replace("DEFINER=`root`@`%` ", "")
            proc = proc.replace("DEFINER=`root`@`localhost` ", "")
            procs = [f'DROP PROCEDURE IF EXISTS `{name}`; ', proc]
            mysql_tools = MysqlHelper(links["target"])
            mysql_tools.updates(procs)


    # ----------------------------常规检测----------------------------------------
    '''
    1、远程管理用户检测
    2、密码过期检测
    '''
    @classmethod
    def other_check(cls):
        pass

    @classmethod
    def check_remote_user(cls, link):
        '''
        查询远程账户
        :return:
        '''
        sql = "select count(1) row from mysql.user where `Host` = %s and `User` = 'root';"
        mysql_tools = MysqlHelper(link)
        res = mysql_tools.get_one(sql, ("%"))
        print(res)
        if res["row"] == 1:
            return True
        else:
            return False

    @classmethod
    def add_remote_user(cls, link):
        '''
        创建远程账户
        :param link:
        :return:
        '''
        sqls = [
            "CREATE USER 'root'@%s IDENTIFIED BY '#chengan2021#';",
            "ALTER USER 'root'@%s PASSWORD EXPIRE;",
            "GRANT ALL PRIVILEGES ON *.* TO 'root'@%s WITH GRANT OPTION;",
            "FLUSH PRIVILEGES;"
        ]
        params = [
            ("%"), ("%"), ("%"), ()
        ]
        mysql_tools = MysqlHelper(link)
        mysql_tools.updates(sqls, params)

if __name__ == '__main__':
    print("Python")
